﻿Imports System.Data
Imports System
Imports System.IO
Imports System.Data.SqlClient

Partial Class Content_Reportes_rep_controls_ReporteCobroHon
    Inherits System.Web.UI.UserControl
    Dim IDataBase As New Clases.DataBase
    Dim ECConnString As String = ConfigurationManager.AppSettings("dbIP")
    Dim IControles As New Clases.controles

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim q1, q2 As String

            q1 = "Select * from TDespachos where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlDespacho, "TDespachos", "desp_id", "desp_nombre", q1)
            IControles.addNoneAllElement(ddlDespacho)

            q2 = "Select * from TInstituciones where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlInstitucion, "TInstituciones", "inst_id", "inst_nombre", q2)
            IControles.addNoneAllElement(ddlInstitucion)

            'hl_pdf.Visible = False
            txtFechaI.Attributes.Add("onkeypress", "return false")
            txtFechaI.Attributes.Add("onPaste", "return false;")
            txtFechaF.Attributes.Add("onkeypress", "return false")
            txtFechaF.Attributes.Add("onPaste", "return false;")
        End If
    End Sub

    Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
        'hl_pdf.Visible = True

        Dim qryBusq As String
        'Dim id, aux, auxi, eauxi As String        
        'aux = "Select cl_id from TClientes where cl_nombre like '" & txtCliente.Text & "%'"
        'id = IDataBase.getSimpleData(aux, "cl_id")
        'auxi = "Select cl_contrato from TClientes where cl_id = " & id
        'eauxi = IDataBase.getSimpleData(auxi, "cl_contrato")
        'txtContrato.Text = eauxi
        'txtContrato.Attributes("style") = "TEXT-ALIGN: center"

        'qryBusq = "SELECT cl.desp_nombre as Despacho, cl.cl_honorarios as Porcentaje, cl.inst_nombre as Institucion, cl.promotor as Promotor, cl.cl_contrato as Contrato, NombreC as Nombre, dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "') AS SaldoP," & _
        '" DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "') AS Periodo, (((dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id))/(360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "'))) as Cuota," & _
        '" ((((dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id))/(360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "'))) * (SELECT Iva_valor FROM Tiva)) as IVA, " & _
        '" ((((dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id))/(360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "'))) +((((dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id))/(360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "'))) * (SELECT Iva_valor FROM Tiva))+(dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "'))) as Comision" & _
        '" FROM vwClientes AS cl WHERE 1 = 1"

        qryBusq = "SELECT cl.desp_nombre as Despacho, cl.inst_nombre as Institucion, cl.promotor as Promotor, cl.cl_contrato as Contrato, NombreC as Nombre, dbo.fnSaldoPromedio( cl.cl_id ,'2012-02-01','2012-02-28') AS SaldoP," & _
        " DATEDIFF(day,'2012-02-01','2012-02-28') AS Periodo, " & _
        " cl.cl_honorarios as Porcentaje, " & _
        " (dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id)/360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')) as Cuota, " & _
        " (dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id)/360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "') * (SELECT Iva_valor FROM Tiva)) as IVA, ((dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id)/360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')) +(dbo.fnSaldoPromedio( cl.cl_id ,'" & txtFechaI.Text & "','" & txtFechaF.Text & "')*(SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id)/360*DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "') * (SELECT Iva_valor FROM Tiva))) as Comision " & _
        " FROM vwClientes AS cl WHERE 1 = 1 "


        'qryBusq = "SELECT cl.desp_nombre as Despacho, cl.cl_honorarios as FEE, cl.inst_nombre as Institucion, cl.promotor, NombreC, dbo.fnSaldoPromedio(cl.cl_id,'" & txtFechaI.Text & "','" & txtFechaF.Text & "', " & sf & ") AS [Saldo Promedio]," & _
        '" DATEDIFF(day,'" & txtFechaI.Text & "','" & txtFechaF.Text & "') AS [Días periodo], ( dbo.fnSaldoPromedio(cl.cl_id,'" & txtFechaI.Text & "','" & txtFechaF.Text & "', " & sf & ") * (SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id) ) AS Comision," & _
        '" ( dbo.fnSaldoPromedio(cl.cl_id,'" & txtFechaI.Text & "','" & txtFechaF.Text & "', " & sf & ") * (SELECT Iva_valor FROM Tiva) ) AS IVA, " & _
        '" ((dbo.fnSaldoPromedio(cl.cl_id,'" & txtFechaI.Text & "','" & txtFechaF.Text & "', " & sf & ") * (SELECT cl_honorarios FROM TClientes WHERE cl_id=cl.cl_id)) +  (dbo.fnSaldoPromedio(cl.cl_id,'" & txtFechaI.Text & "','" & txtFechaF.Text & "', " & sf & ") * (SELECT Iva_valor FROM Tiva))) AS [Total Comision]" & _
        '" FROM vwClientes AS cl WHERE 1 = 1"

        'qryBusq = "Select desp_nombre, inst_nombre,cl_nombre,cl_contrato, SaldoProm, Dias, FEE,Comision,IVA,Total from vwHonorarios where cl_id=" & id & " "

        If ddlDespacho.SelectedValue <> "None" Then
            qryBusq = qryBusq & " and desp_id=" & ddlDespacho.SelectedValue & ""
        End If

        If ddlInstitucion.SelectedValue <> "None" Then
            qryBusq = qryBusq & " and inst_id=" & ddlInstitucion.SelectedValue & ""
        End If

        'If txtCliente.Text <> "" Then
        '    qryBusq = qryBusq & " and NombreC like '" & txtCliente.Text & "%'"
        'End If

        'If (txtFechaI.Text <> "") And (txtFechaF.Text <> "") Then
        '    qryBusq = qryBusq & " and movi_fecha_op between '" & txtFechaI.Text & "' and '" & txtFechaF.Text & "'"
        'End If

        'If (txtFechaI.Text <> "") And (txtFechaF.Text = "") Then
        '    qryBusq = qryBusq & " and movi_fecha_op between '" & txtFechaI.Text & "' and getdate()"
        'End If

        Session("RepHon") = qryBusq

        IControles.fillGridViewPaging(gvHonorarios, qryBusq)
        'hl_pdf.Visible = True
        btn_excel.Visible = True
    End Sub

    Protected Sub exportToExcel(ByVal query As String)
        Dim gridView_toExport As New GridView
        IControles.fillGridViewPaging(gridView_toExport, Session("RepHon"))

        For i As Integer = 0 To gridView_toExport.Rows.Count - 1
            Dim row As GridViewRow = gridView_toExport.Rows(i)
        Next

        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As IO.StringWriter = New IO.StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page
        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)
        gridView_toExport.AllowPaging = False
        form.Controls.Add(gridView_toExport)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"

        Response.AddHeader("Content-Disposition", "attachment;filename=Honorarios" & Now.ToString("_dd_MMMM_yy_HH_mm_ss") & ".xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    Protected Sub btn_excel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btn_excel.Click
        'exportToExcel(gvHonorarios)
        exportToExcel(Session("RepHon"))
    End Sub

End Class
